//
//  TheHomeManager+Additions.m
//  JKFuYinProject
//
//  Created by jiukuyinyue on 2017/10/11.
//  Copyright © 2017年 LC. All rights reserved.
//

#import "TheHomeManager+Additions.h"

@implementation TheHomeManager (Additions)

- (NSInteger)getBibleIDWithSelectTitle:(NSString *)selectTitle{
    
    NSInteger numBibleID = 0;
    
    if (!self.db)
    {
        NSLog(@"数据库创建不成功");
    }else{
        if([self.db open])
        {
            NSLog(@"数据库创建成功并打开");
            //查询圣经歌词
            NSString *sql3 = [NSString stringWithFormat:@"SELECT * FROM `Song` WHERE title = '%@'",selectTitle];
            FMResultSet *result3 = [self.db executeQuery:sql3];
            while ([result3 next])
            {
                numBibleID = [result3 intForColumn:@"id"];
            }
            [self.db close];
        }
    }
    return numBibleID;
}

- (NSInteger)getZhangJieNumWithSelectTitle:(NSString *)selectTitle{
    NSInteger numZhangJie= 0;
    if (!self.db)
    {
        NSLog(@"数据库创建不成功");
    }else{
        if([self.db open])
        {
            NSLog(@"数据库创建成功并打开");
            //查询圣经歌词
            NSString *sql3 = [NSString stringWithFormat:@"SELECT COUNT(*) FROM `Song` WHERE title like '%@%@%@'",@"%",selectTitle,@"%"];
            FMResultSet *result3 = [self.db executeQuery:sql3];
            while ([result3 next])
            {
                numZhangJie = [result3 intForColumn:@"COUNT(*)"];
            }
            [self.db close];
        }
    }
    return numZhangJie;
}
- (NSString *)getJieIDWithSelectContent:(NSString *)selectContent shengJingTitle:(NSString *)shengJingTitle{
    NSString *JieID;
    if (!self.db)
    {
        NSLog(@"数据库创建不成功");
    }else{
        if([self.db open])
        {
            NSLog(@"数据库创建成功并打开");
            //查询圣经JieID
            NSString *sql3 = [NSString stringWithFormat:@"SELECT id FROM `Lyric` WHERE content like '%@%@%@' AND parentId in (SELECT id FROM `Song` WHERE title = '%@')",@"%",selectContent,@"%",shengJingTitle];
            //            NSLog(@"sql3 = %@", sql3);
            FMResultSet *result3 = [self.db executeQuery:sql3];
            while ([result3 next])
            {
                JieID = [result3 stringForColumn:@"id"];
            }
            [self.db close];
        }
    }
    return JieID;
}

//查询圣经歌词
- (NSMutableString *)getShengJingLrc:(NSInteger)selectID{
    
    NSMutableString *mutableString = [[NSMutableString alloc] init];
    
    if (!self.db)
    {
        NSLog(@"数据库创建不成功");
    }else{
        if([self.db open])
        {
            NSLog(@"数据库创建成功并打开");
            //查询圣经歌词
            NSString *sql3 = [NSString stringWithFormat:@"SELECT * FROM `Lyric` WHERE PARENTID = %zd",selectID];
            FMResultSet *result3 = [self.db executeQuery:sql3];
            while ([result3 next])
            {
                
                NSString * timeStr = [result3 stringForColumn:@"startTime"];
                NSInteger durationTime = [timeStr integerValue];
                NSInteger num1 = durationTime/60000;
                NSInteger num2 = durationTime%60000/1000;
                NSString * startTimeStr = nil;
                if (num1<10) {
                    if (num2<10) {
                        startTimeStr = [NSString stringWithFormat:@"0%ld:0%ld", (long)num1, (long)num2];
                    }else
                        startTimeStr = [NSString stringWithFormat:@"0%ld:%ld", (long)num1, (long)num2];
                }else{
                    if (num2<10) {
                        startTimeStr = [NSString stringWithFormat:@"%ld:0%ld", (long)num1, (long)num2];
                    }else
                        startTimeStr = [NSString stringWithFormat:@"%ld:%ld", (long)num1, (long)num2];
                }
                [mutableString appendString:[NSString stringWithFormat:@"[%@] %@",startTimeStr,[result3 stringForColumn:@"content"]]];
                
            }
            
            [self.db close];
            
        }
    }
    
    return mutableString;
    
}
//查询圣经 ParentId
- (NSMutableString *)getSongParentIdWithTitle:(NSString *)title{
    
    NSMutableString *mutableString = [[NSMutableString alloc] init];
    
    if (!self.db)
    {
        NSLog(@"数据库创建不成功");
    }else{
        if([self.db open]){
            NSLog(@"数据库创建成功并打开");
            //查询圣经歌词
            NSString *sql3 = [NSString stringWithFormat:@"SELECT * FROM `Song` WHERE title = '%@'",title];
            FMResultSet *result3 = [self.db executeQuery:sql3];
            while ([result3 next])
            {
                NSLog(@" parentId  %@", [result3 stringForColumn:@"parentId"]);
                  [mutableString appendString:[NSString stringWithFormat:@"%@",[result3 stringForColumn:@"parentId"]]];
            }
            [self.db close];
        }
    }
    NSLog(@"mutableString = %@", mutableString);
    return mutableString;
}
///搜索圣经
- (NSDictionary *)searchOldAllWithStr:(NSString *)str{
    
    NSString *dbPath = [[NSBundle mainBundle]
                        pathForResource:@"shengjing"
                        ofType:@"db"];
    self.db= [FMDatabase databaseWithPath:dbPath];
    
    NSMutableArray *mutableArray = [[NSMutableArray alloc] init];
    NSDictionary *dictionary;
    NSMutableArray *titleArray = [[NSMutableArray alloc] init];
    NSMutableArray *contentArray = [[NSMutableArray alloc] init];

    if (!self.db)
    {
        NSLog(@"数据库创建不成功");
        
    }else{
        if([self.db open]){
            NSLog(@"数据库创建成功并打开");
            //查询圣经歌词
            NSString *sql3 = [NSString stringWithFormat:@"SELECT * FROM `Lyric` where parentId < 930;"];

            FMResultSet *result3 = [self.db executeQuery:sql3];
            while ([result3 next])            {
                NSString * content = [result3 stringForColumn:@"content"];
                NSRange range = [content rangeOfString:str];
                if (range.length > 0) {
                    NSString *parentId = [result3 stringForColumn:@"parentId"];
                    if ([mutableArray containsObject:parentId]) {
                    }else{
                        [mutableArray addObject:parentId];
                        [contentArray addObject:[NSString stringWithFormat:@"%@",content]];
                    }
                    
                }
            }
            for (NSInteger i = 0; i < mutableArray.count; i++) {
                //查询圣经title
                NSString *sql4 = [NSString stringWithFormat:@"SELECT * FROM `Song` where id = ?;"];

                FMResultSet *result4 = [self.db executeQuery:sql4,mutableArray[i]];
                while ([result4 next])            {
                    NSString *titleStr = [result4 stringForColumn:@"title"];
                    
                    NSRange range = [titleStr rangeOfString:@"旧约"];
                    if (range.length > 0) {
                        [titleArray addObject:[NSString stringWithFormat:@"%@",titleStr]];
                    }
                }
                
            }
            
        }
                        dictionary = @{
                                     @"title":titleArray,
                                     @"content":contentArray,
                                     @"parentId":mutableArray
                                     };
        [self.db close];

    }
    return dictionary;
}

///搜索圣经
- (NSDictionary *)searchNewAllWithStr:(NSString *)str{
    
    NSString *dbPath = [[NSBundle mainBundle]
                        pathForResource:@"shengjing"
                        ofType:@"db"];
    self.db= [FMDatabase databaseWithPath:dbPath];
    
    NSMutableArray *mutableArray = [[NSMutableArray alloc] init];
    NSDictionary *dictionary;
    NSMutableArray *titleArray = [[NSMutableArray alloc] init];
    NSMutableArray *contentArray = [[NSMutableArray alloc] init];
    
    if (!self.db)
    {
        NSLog(@"数据库创建不成功");
        
    }else{
        if([self.db open]){
            NSLog(@"数据库创建成功并打开");
            //查询圣经歌词
            NSString *sql3 = [NSString stringWithFormat:@"SELECT * FROM `Lyric` where parentId >= 930;"];
            
            FMResultSet *result3 = [self.db executeQuery:sql3];
            while ([result3 next])            {
                NSString * content = [result3 stringForColumn:@"content"];
                NSRange range = [content rangeOfString:str];
                if (range.length > 0) {
                    NSString *parentId = [result3 stringForColumn:@"parentId"];
                    if ([mutableArray containsObject:parentId]) {
                    }else{
                        [mutableArray addObject:parentId];
                        [contentArray addObject:[NSString stringWithFormat:@"%@",content]];
                    }
                    
                }
            }
            for (NSInteger i = 0; i < mutableArray.count; i++) {
                //查询圣经title
                NSString *sql4 = [NSString stringWithFormat:@"SELECT * FROM `Song` where id = ?;"];
                
                FMResultSet *result4 = [self.db executeQuery:sql4,mutableArray[i]];
                while ([result4 next])            {
                    NSString *titleStr = [result4 stringForColumn:@"title"];
                    
                    NSRange range = [titleStr rangeOfString:@"新约"];
                    if (range.length > 0) {
                        [titleArray addObject:[NSString stringWithFormat:@"%@",titleStr]];
                    }
                }
                
            }
            
        }
            
         dictionary = @{
                       @"title":titleArray,
                       @"content":contentArray,
                       @"parentId":mutableArray
                       };
        [self.db close];
        
    }
    return dictionary;
}
@end
